package cn.sfa.service.product;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
 
import cn.sfa.entity.affixItem.SaleAffixItem;
import cn.sfa.entity.manage.FastProductInfo;
import cn.sfa.entity.manage.SaleProduct;

/**
 *	产品相关
 * @author hui
 * @version 	
 */
@Service
@Transactional
public class ProductService {

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

	/**
	 * 查询saleproduct信息(前台)
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param productCode
	 * @param productTitle
	 * @param provides
	 * @param currentBranchId
	 * @param channeltype
	 * @param salestatus
	 * @return
	 * @author hui
	 */
	public Pagination SeachSaleProduct(
			int currentPage, 	// 页码
			int numPerPage,		// 页尺寸
			String url,			// 分页url
			String productCode, // 产品代码
			String productTitle,// 产品名称
			String provides, 	// 供应商
			String currentBranchId, //当前登录人的机构ID
			String channeltype, // 销售渠道
			String salestatus 	// 销售状态
	) {
		Map<String, Object> paraMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select * from table(pkg_sfa_saleproudct.getSaleProduct(:currentBranchId,:productCode,:productTitle,:provides,:channeltype,:salestatus)) "); 
		paraMap.put("productCode", productCode); 
		paraMap.put("productTitle", productTitle); 
		paraMap.put("provides", provides); 
		paraMap.put("currentBranchId", currentBranchId); 
		paraMap.put("channeltype", channeltype); 
		paraMap.put("salestatus", salestatus); 
		return new Pagination(sqlStr.toString(), paraMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	/**
	 * 查询fast产品信息（后台）
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param branchId
	 * @param productCode
	 * @param productTitle
	 * @param providerStr
	 * @param datebegin
	 * @param dateend
	 * @param stopdatebegin
	 * @param stopdateend
	 * @param releasetatus
	 * @param currentBranchId
	 * @param channeltype
	 * @param salestatus
	 * @return
	 * @author hui
	 */
	public Pagination SeachProduct(
			int currentPage,	//页码
			int numPerPage,  	//页尺寸
			String url,     	//分页url
			String branchId,  	//机构ID
			String productCode, //产品代码
			String productTitle,//产品名称
			String providerStr, //供应商
			String datebegin,   //起售日期始
			String dateend,     //起售日期止
			String stopdatebegin,//停售日期始
			String stopdateend, //停售日期止
			String releasetatus,//状态,
			String currentBranchId,//当前登录人的机构ID
			String channeltype, //销售渠道
			String salestatus 	//销售状态
	) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select * from table(pkg_sfa_saleproudct.getfastproduct(:branchId,:productCode,:productTitle,:providerStr,:datebegin,:dateend,:stopdatebegin,:stopdateend,:releasetatus,:channeltype,:salestatus,:currentBranchId))");	
		paramMap.put("branchId", branchId);
		paramMap.put("productCode", productCode);
		paramMap.put("productTitle", productTitle);
		paramMap.put("providerStr", providerStr);
		paramMap.put("datebegin", datebegin);
		paramMap.put("dateend", dateend);
		paramMap.put("stopdatebegin", stopdatebegin);
		paramMap.put("stopdateend", stopdateend);
		paramMap.put("releasetatus", releasetatus);
		paramMap.put("currentBranchId", currentBranchId);
		paramMap.put("channeltype", channeltype);
		paramMap.put("salestatus", salestatus);
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	/*
	 * 根据ID获取Fast中的产品信息
	 */
	public FastProductInfo getFastProductByID(String productId) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select ");
		sqlStr.append(" p.productid, ");
		sqlStr.append(" p.createuserid, ");
		sqlStr.append(" p.createdate, ");
		sqlStr.append(" p.updateuserid, ");
		sqlStr.append(" p.updatedate, ");
		sqlStr.append(" p.productcode, ");
		sqlStr.append(" p.productname, ");
		sqlStr.append(" p.abbrname, ");
		sqlStr.append(" p.producttype, ");
		sqlStr.append(" scType.chnname producttypename, ");
		sqlStr.append(" p.providerid, ");
		sqlStr.append(" prd.providername, ");
		sqlStr.append(" p.salestatus, ");
		sqlStr.append(" scStatus.chnname salestatusname, ");
		sqlStr.append(" p.salestartdate, ");
		sqlStr.append(" p.saleenddate ");
		sqlStr.append(" from product p ");
		sqlStr.append(" left join syscode scType on scType.recordid = p.producttype ");
		sqlStr.append(" left join provider prd on prd.providerid = p.providerid ");
		sqlStr.append(" left join syscode scStatus on scStatus.recordid = p.salestatus ");
		sqlStr.append(" where p.productid = :productid ");

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("productid", productId);

		final FastProductInfo productInfo = new FastProductInfo();
		jdbcTemplate.query(sqlStr.toString(), paramMap,
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						productInfo.setProductid(rs.getString("productid"));// 产品ID
						productInfo.setProductcode(rs.getString("productcode"));// 产品编码
						productInfo.setProductname(rs.getString("productname"));// 产品名称
						productInfo.setAbbrname(rs.getString("abbrname"));// 产品简称
						productInfo.setProducttype(rs.getString("producttype"));// 产品类型
						productInfo.setProducttypename(rs
								.getString("producttypename"));// 产品类型名称
						productInfo.setProviderid(rs.getString("providerid"));// 供应商ID
						productInfo.setProvidername(rs
								.getString("providername"));// 供应商名称
						productInfo.setSalestatus(rs.getString("salestatus"));// 产品销售状态
						productInfo.setSalestatusname(rs
								.getString("salestatusname"));// 产品销售状态名称
						productInfo.setSalestartdate(rs
								.getDate("salestartdate"));// 产品起售时间
						productInfo.setSaleenddate(rs.getDate("saleenddate"));// 产品停售时间
					}
				});
		return productInfo;
	}
	
	/**
	 * 根据Code获取Fast中的产品信息
	 */
	public FastProductInfo getFastProductByCode(String productCode) {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select ");
		sqlStr.append(" p.productid, ");
		sqlStr.append(" p.createuserid, ");
		sqlStr.append(" p.createdate, ");
		sqlStr.append(" p.updateuserid, ");
		sqlStr.append(" p.updatedate, ");
		sqlStr.append(" p.productcode, ");
		sqlStr.append(" p.productname, ");
		sqlStr.append(" p.abbrname, ");
		sqlStr.append(" p.producttype, ");
		sqlStr.append(" scType.chnname producttypename, ");
		sqlStr.append(" p.providerid, ");
		sqlStr.append(" prd.providername, ");
		sqlStr.append(" p.salestatus, ");
		sqlStr.append(" scStatus.chnname salestatusname, ");
		sqlStr.append(" p.salestartdate, ");
		sqlStr.append(" p.saleenddate ");
		sqlStr.append(" from product p ");
		sqlStr
				.append(" left join syscode scType on scType.recordid = p.producttype ");
		sqlStr
				.append(" left join provider prd on prd.providerid = p.providerid ");
		sqlStr
				.append(" left join syscode scStatus on scStatus.recordid = p.salestatus ");
		sqlStr.append(" where p.productcode = :productCode ");

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("productCode", productCode);

		final FastProductInfo productInfo = new FastProductInfo();
		jdbcTemplate.query(sqlStr.toString(), paramMap,
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						productInfo.setProductid(rs.getString("productid"));// 产品ID
						productInfo.setProductcode(rs.getString("productcode"));// 产品编码
						productInfo.setProductname(rs.getString("productname"));// 产品名称
						productInfo.setAbbrname(rs.getString("abbrname"));// 产品简称
						productInfo.setProducttype(rs.getString("producttype"));// 产品类型
						productInfo.setProducttypename(rs
								.getString("producttypename"));// 产品类型名称
						productInfo.setProviderid(rs.getString("providerid"));// 供应商ID
						productInfo.setProvidername(rs
								.getString("providername"));// 供应商名称
						productInfo.setSalestatus(rs.getString("salestatus"));// 产品销售状态
						productInfo.setSalestatusname(rs
								.getString("salestatusname"));// 产品销售状态名称
						productInfo.setSalestartdate(rs
								.getDate("salestartdate"));// 产品起售时间
						productInfo.setSaleenddate(rs.getDate("saleenddate"));// 产品停售时间
					}
				});
		return productInfo;
	}

	/*
	 * 根据id判断这个产品是否已经从Fast发布到了系统中
	 */
	public boolean getIsPublished(String productId) {
		String sqlStr = "select count(1) from saleproduct where productid = :productid ";

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("productid", productId);

		return jdbcTemplate.queryForInt(sqlStr, paramMap) > 0;
	}

	/*
	 * 发布产品
	 */
	public boolean insertSaleProduct(SaleProduct model) {
		String sqlStr = "INSERT INTO SALEPRODUCT(SALEPRODUCTID,CREATEDATE,CREATEUSER,PRODUCTID,PRODUCTCODE,PRODUCTNAME,ISAFFIX,OTHERINFO,SALESTATUS) "
				+ "VALUES("
				+ ":SALEPRODUCTID,"
				+ ":CREATEDATE,"
				+ ":CREATEUSER,"
				+ ":PRODUCTID,"
				+ ":PRODUCTCODE,"
				+ ":PRODUCTNAME," + ":ISAFFIX," + ":OTHERINFO,:SALESTATUS)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALEPRODUCTID", model.getSaleproductid());
		paramMap.put("CREATEDATE", model.getCreateDate());
		paramMap.put("CREATEUSER", CommonUtil.jugeAndFixNull(model
				.getCreateUser()));
		paramMap.put("PRODUCTID", CommonUtil.jugeAndFixNull(model
				.getProductid()));
		paramMap.put("PRODUCTCODE", CommonUtil.jugeAndFixNull(model
				.getProductcode()));
		paramMap.put("PRODUCTNAME", CommonUtil.jugeAndFixNull(model
				.getProductname()));
		paramMap.put("ISAFFIX", CommonUtil.jugeAndFixNull(model.getIsAffix()));
		paramMap.put("SALESTATUS", CommonUtil.jugeAndFixNull(model
				.getSalestatus()));
		paramMap.put("OTHERINFO", CommonUtil.jugeAndFixNull(model.getOtherinfo()));
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}

	/*
	 * 修改发布的产品
	 */
	public boolean updateSaleProduct(SaleProduct model) {
		String sqlStr = "UPDATE SALEPRODUCT SET " + "UPDATEDATE=:UPDATEDATE,"
				+ "UPDATEUSER=:UPDATEUSER," + "PRODUCTID=:PRODUCTID,"
				+ "PRODUCTCODE=:PRODUCTCODE," + "PRODUCTNAME=:PRODUCTNAME,"
				+ "ISAFFIX=:ISAFFIX, " + "OTHERINFO=:OTHERINFO, "+"SALESTATUS=:SALESTATUS" + " WHERE SALEPRODUCTID=:SALEPRODUCTID";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("UPDATEDATE", model.getUpdateDate());
		paramMap.put("UPDATEUSER", CommonUtil.jugeAndFixNull(model
				.getUpdateUser()));
		paramMap.put("PRODUCTID", CommonUtil.jugeAndFixNull(model
				.getProductid()));
		paramMap.put("PRODUCTCODE", CommonUtil.jugeAndFixNull(model
				.getProductcode()));
		paramMap.put("PRODUCTNAME", CommonUtil.jugeAndFixNull(model
				.getProductname()));
		paramMap.put("ISAFFIX", CommonUtil.jugeAndFixNull(model.getIsAffix()));
		paramMap.put("SALEPRODUCTID", model.getSaleproductid());
		paramMap.put("OTHERINFO", CommonUtil.jugeAndFixNull(model.getOtherinfo()));
		paramMap.put("SALESTATUS", CommonUtil.jugeAndFixNull(model
				.getSalestatus()));
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}

	/**
	 * 停售产品
	 * 
	 * @param productId
	 */
	public boolean stopNotice(String productId) {
		String sqlStr = " update saleproduct set salestatus ='SALESTATUS_SA' where productid =:productId";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("productId", productId);
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}

	/*
	 * 根据ID获取SaleProduct
	 */
	public SaleProduct getSaleProductByproductId(String productId) {
		String sqlStr = "select * from table(pkg_sfa_getProductByproductId.getProductByproductId(:productid))";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("productid", productId);

		final SaleProduct saleProduct = new SaleProduct();
		jdbcTemplate.query(sqlStr, paramMap, new RowCallbackHandler() {

			public void processRow(ResultSet rs) throws SQLException {

				saleProduct.setSaleproductid(rs.getString("SALEPRODUCTID"));// 记录ID
				saleProduct.setCreateDate(rs.getDate("CREATEDATE"));// 创建日期
				saleProduct.setCreateUser(rs.getString("CREATEUSER"));// 创建用户

				saleProduct.setUpdateDate(rs.getDate("UPDATEDATE"));// 更新日期
				saleProduct.setUpdateUser(rs.getString("UPDATEUSER"));// 更新用户
				saleProduct.setProductid(rs.getString("PRODUCTID"));// FAST产品ID
				saleProduct.setProductcode(rs.getString("PRODUCTCODE"));// 产品编码
				saleProduct.setProductname(rs.getString("PRODUCTNAME"));// 产品名称
				saleProduct.setIsAffix(rs.getString("ISAFFIX"));// 是否包含附件
				saleProduct.setSalestatus(rs.getString("salestatus"));
				saleProduct.setOtherinfo(rs.getString("OTHERINFO"));
			}
		});
		return saleProduct;
	}

	/**
	 * 关联产品信息
	 */
	public Pagination RelevanceSaleProduct(int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String productCode, // 产品代码
			String productTitle,// 产品名称
			String productProvider,// 供应商
			String currentBranchId // 当前登录人的机构ID
	) {
		Map<String, Object> paraMap = new HashMap<String, Object>();
		String sqlStr = "select providername,providerid,productcode,productid,productname,saleproductid,isaffix " +
				"from table(pkg_sfa_RelevanceSaleProduct.relevanceSaleProduct " +
				"(:productCode,:productTitle,:productProvider,:currentBranchId))";
		paraMap.put("productCode", productCode);
		paraMap.put("productTitle", productTitle);
		paraMap.put("productProvider", productProvider);
		paraMap.put("currentBranchId", currentBranchId);
		
		return new Pagination(sqlStr, paraMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}
	public List<SaleAffixItem> getAffix(String tabName,String mainId){
		String sqlStr = "select SALEAFFIXITEMID,CREATEDATE,CREATEUSER,"+
				"UPDATEDATE,UPDATEUSER,AFFIXMAINTABNAME,AFFIXMAINID,"+
				"SALEAFFIXITEMFILEPATH,SALEAFFIXITEMFILENAME,ISTRUE from " +
				"table(pkg_sfa_RelevanceSaleProduct.getAffix(:AFFIXMAINTABNAME,:AFFIXMAINID))";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("AFFIXMAINTABNAME", tabName);
		paramMap.put("AFFIXMAINID", mainId); 
		
 		List<SaleAffixItem> affixList=new ArrayList<SaleAffixItem>();
 		affixList=jdbcTemplate.query(sqlStr,paramMap,new BeanPropertyRowMapper<SaleAffixItem>(SaleAffixItem.class));
 		System.out.println(affixList.size());
		return affixList;
	}	
	
	public List<SaleAffixItem> getAffixForPlanBook(String tabName,String mainId){
		String sqlStr = "select SALEAFFIXITEMID,CREATEDATE,CREATEUSER,"+
				"UPDATEDATE,UPDATEUSER,AFFIXMAINTABNAME,AFFIXMAINID,"+
				"SALEAFFIXITEMFILEPATH,SALEAFFIXITEMFILENAME,ISTRUE from " +
				"table(pkg_sfa_RelevanceSaleProduct.getAffixForPlanBook(:AFFIXMAINTABNAME,:AFFIXMAINID))";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("AFFIXMAINTABNAME", tabName);
		paramMap.put("AFFIXMAINID", mainId); 
		
		List<SaleAffixItem> affixList=new ArrayList<SaleAffixItem>();
		affixList=jdbcTemplate.query(sqlStr,paramMap,new BeanPropertyRowMapper<SaleAffixItem>(SaleAffixItem.class));
		System.out.println(affixList.size());
		return affixList;
	}	
}
